/*
 * Copyright 2012 Subhabrata Ghosh <subho.ghosh at langene.net>.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */



package net.langene.nlp.pg;

//~--- non-JDK imports --------------------------------------------------------

import net.langene.nlp.Context;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

//~--- JDK imports ------------------------------------------------------------

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.LinkedList;
import java.util.List;

//~--- classes ----------------------------------------------------------------

/**
 *
 * @author Subhabrata Ghosh <subho.ghosh at langene.net>
 */
public class PGDBHelper {
    private static final Logger	_logger = LoggerFactory.getLogger(PGDBHelper.class);

    //~--- get methods --------------------------------------------------------

    /**
     * Get the DB Item record for the specified ID.
     * 
     * @param id Item ID
     * @return
     * @throws Exception
     */
    public static PGItem getItem(String id) throws Exception {
        Connection	dbconn    = Context.get().getConnection();
        String		selectsql = "SELECT pg_index.indexid, pg_index.url, pg_index.title, "
                           + " pg_index.processed, pg_index.timestamp, pg_index.parsedfile "
                           + " FROM langene_nlp.pg_index " + " WHERE pg_index.indexid = ? ";
        PreparedStatement	pstmnt = dbconn.prepareStatement(selectsql);

        pstmnt.setString(1, id);

        PGItem		item = null;
        ResultSet	rs   = pstmnt.executeQuery();

        while (rs.next()) {
            item            = new PGItem();
            item.Id         = id;
            item.Url        = rs.getString(2);
            item.Title      = rs.getString(3);
            item.Processed  = rs.getBoolean(4);
            item.Timestamp  = rs.getTimestamp(5);
            item.ParsedFile = rs.getString(6);

            break;
        }

        rs.close();
        pstmnt.close();

        return item;
    }

    //~--- methods ------------------------------------------------------------

    /**
     * Save the item to the DB. (Create/Update)
     * 
     * @param item
     * @throws Exception
     */
    public static void save(PGItem item) throws Exception {
        _logger.debug("Saving item : ID : " + item.Id + ", URL : " + item.Url);

        Connection	dbconn  = Context.get().getConnection();
        PGItem		current = getItem(item.Id);

        if (current == null) {

            // Create new DB record
            String	insertsql = "INSERT INTO langene_nlp.pg_index "
                               + " (indexid, url, title, processed, timestamp, parsedfile) "
                               + " VALUES ( ?, ?, ?, ?, ?, ?)";
            PreparedStatement	pstmnt = dbconn.prepareStatement(insertsql);

            pstmnt.setString(1, item.Id);
            pstmnt.setString(2, item.Url);
            pstmnt.setString(3, item.Title);
            pstmnt.setBoolean(4, item.Processed);
            pstmnt.setTimestamp(5, item.Timestamp);
            pstmnt.setString(6, item.ParsedFile);
            pstmnt.executeUpdate();
            pstmnt.close();
        } else {

            // Update DB record
            String	updatesql = "UPDATE langene_nlp.pg_index SET " + " url = ?, " + " title = ?, "
                               + " processed = ?, " + " timestamp = ?, " + " parsedfile = ? "
                               + " WHERE indexid = ? ";
            PreparedStatement	pstmnt = dbconn.prepareStatement(updatesql);

            pstmnt.setString(1, item.Url);
            pstmnt.setString(2, item.Title);
            pstmnt.setBoolean(3, item.Processed);
            pstmnt.setTimestamp(4, item.Timestamp);
            pstmnt.setString(5, item.ParsedFile);
            pstmnt.setString(6, item.Id);
            pstmnt.executeUpdate();
            pstmnt.close();
        }
    }

    //~--- get methods --------------------------------------------------------

    /**
     * Get all records for which text input has been created
     * but haven't been parsed yet.
     * 
     * @return
     * @throws Exception
     */
    public static List<PGItem> getItemUnparsedRecords() throws Exception {
        Connection	dbconn    = Context.get().getConnection();
        String		selectsql = "SELECT pg_index.indexid, pg_index.url, pg_index.title, "
                           + " pg_index.processed, pg_index.timestamp, pg_index.parsedfile "
                           + " FROM langene_nlp.pg_index "
                           + " WHERE pg_index.processed = true AND pg_index.parsedfile IS NULL ";
        PreparedStatement	pstmnt = dbconn.prepareStatement(selectsql);
        ResultSet		rs     = pstmnt.executeQuery();
        List<PGItem>		items  = new LinkedList<PGItem>();

        while (rs.next()) {
            PGItem	item = new PGItem();

            item.Id         = rs.getString(1);
            item.Url        = rs.getString(2);
            item.Title      = rs.getString(3);
            item.Processed  = rs.getBoolean(4);
            item.Timestamp  = rs.getTimestamp(5);
            item.ParsedFile = rs.getString(6);
            items.add(item);
        }

        rs.close();
        pstmnt.close();

        return items;
    }

    /**
     * Get all processed/unprocessed records from the DB.
     * 
     * @param processed Processed or Unprocessed?
     * @return
     * @throws Exception
     */
    public static List<PGItem> getItemRecords(boolean processed) throws Exception {
        Connection	dbconn    = Context.get().getConnection();
        String		selectsql = "SELECT pg_index.indexid, pg_index.url, pg_index.title, "
                           + " pg_index.processed, pg_index.timestamp, pg_index.parsedfile "
                           + " FROM langene_nlp.pg_index " + " WHERE pg_index.processed = ? ";
        PreparedStatement	pstmnt = dbconn.prepareStatement(selectsql);

        pstmnt.setBoolean(1, processed);

        ResultSet	rs    = pstmnt.executeQuery();
        List<PGItem>	items = new LinkedList<PGItem>();

        while (rs.next()) {
            PGItem	item = new PGItem();

            item.Id         = rs.getString(1);
            item.Url        = rs.getString(2);
            item.Title      = rs.getString(3);
            item.Processed  = rs.getBoolean(4);
            item.Timestamp  = rs.getTimestamp(5);
            item.ParsedFile = rs.getString(6);
            items.add(item);
        }

        rs.close();
        pstmnt.close();

        return items;
    }
}


//~ Formatted by Jindent --- http://www.jindent.com
